"""
Case Type   : GSC功能模块
Case Name   : file_fdw foreign外表缓存功能验证
Create At   : 2024/10/26
Owner       : @lonely-dance
Description :
    1.配置数据库参数
    2.重启数据库生效
    3.创建外表文件
    4.创建外表
    5.查询系统缓存统计信息
    6.清理环境
Expect      :
    1.修改参数成功
    2.重启成功
    3.成功
    4.成功
    5.成功
    6.成功
History     :
    Modified by peilinqian at 2024-12-6:优化用例，改变新增文件路径，以保障有权限操作
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class GscTestCase(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.com = Common('PrimaryDbUser')
        self.constant = Constant()
        self.pri_dbuser = Node('PrimaryDbUser')
        self.init_para1 = None
        self.is_reset_para = False
        self.file = os.path.join(macro.DB_BACKUP_PATH, "test.data")
        self.svc_name = 'svc_gsc_case0063'
        self.tb_name = 't_gsc_case0063'

    def test_GSC(self):
        text = '----查看初始配置值;----'
        self.log.info(text)
        self.init_para1 = self.com.show_param('enable_global_syscache')
        self.log.info(self.init_para1)

        if self.init_para1 == 'off':
            text = '----step1:打开gsc开关; expect:修改成功----'
            self.log.info(text)
            result = self.pri_sh.execute_gsguc('set',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               f"enable_global_syscache= on")
            self.assertTrue(result, '执行失败:' + text)

            text = '----step2:重启数据库生效; expect:重启成功----'
            self.log.info(text)
            restart = self.pri_sh.restart_db_cluster()
            self.assertTrue(restart, '执行失败:' + text)
            self.is_reset_para = True
        else:
            text = '----gsc开关设置正常，跳过step1、step2----'
            self.log.info(text)
            self.is_reset_para = False

        text = '----step3:创建外表文件 expect:成功----'
        self.log.info(text)
        file_content = '0,column2,column3\n' \
                       '1,test1,test1\n' \
                       '2,test2,test2\n' \
                       '3,test3,test3'
        write_cmd = f"echo -e '{file_content}' > {self.file}"
        self.log.info(write_cmd)
        res = self.pri_dbuser.sh(write_cmd).result()
        self.assertEqual('', res, '执行失败:' + text)

        text = '----step4:创建服务 expect:成功----'
        self.log.info(text)
        cmd = f"drop server if exists {self.svc_name} cascade; " \
              f"create server {self.svc_name} foreign data wrapper file_fdw;"
        self.log.info(cmd)
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.create_server_success, res,
                      '执行失败:' + text)

        text = '----step5:创建外表 expect:成功----'
        self.log.info(text)
        cmd = f"drop foreign table if exists {self.tb_name};" \
              f"create foreign table {self.tb_name} (column1 int," \
              f"column2 char(20),column3 char(20)) " \
              f"server {self.svc_name} options (filename '{self.file}'," \
              f"format 'csv', header 'true');"
        self.log.info(cmd)
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_FOREIGN_SUCCESS_MSG, res,
                      '执行失败:' + text)

        text = '----step6:查询外表数据; expect:成功----'
        self.log.info(text)
        cmd = f"select * from {self.tb_name}"
        self.log.info(cmd)
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn('(3 rows)', res, '执行失败:' + text)

        text = '----step7:查询系统缓存统计信息; expect:成功----'
        self.log.info(text)
        cmd = f"select * from gs_gsc_table_detail() where " \
              f"relname = '{self.tb_name}';"
        self.log.info(cmd)
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn('(1 row)', res, '执行失败:' + text)

    def tearDown(self):
        step1_txt = '----step8:清理环境; expect:成功----'
        self.log.info(step1_txt)
        cmd = f"drop foreign table if exists {self.tb_name};" \
              f"drop server if exists {self.svc_name} cascade;"
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)

        step2_txt = '----step9:删除文件; expect:删除成功----'
        self.log.info(step2_txt)
        file_rm_cmd = f'rm -rf {self.file};' \
                      f'if [ -f {self.file} ]; ' \
                      f'then echo "exists"; else echo "not exists"; fi'
        self.log.info(file_rm_cmd)
        file_rm_result = self.pri_dbuser.sh(file_rm_cmd).result()
        self.log.info(file_rm_result)

        if self.is_reset_para:
            self.step3_txt = '----step10:恢复参数为初始值并查询; expect:设置成功----'
            self.log.info(self.step3_txt)
            self.msg1 = self.pri_sh.execute_gsguc('set',
                                                  self.constant.GSGUC_SUCCESS_MSG,
                                                  f"enable_global_syscache "
                                                  f"={self.init_para1}")
            self.restart = self.pri_sh.restart_db_cluster()
            self.status_result = self.pri_sh.get_db_cluster_status('status')

        self.log.info(f'----{os.path.basename(__file__)} end----')
        self.assertIn(self.constant.drop_server_success, res,
                      '执行失败:' + step1_txt)
        self.assertIn(self.constant.DROP_FOREIGN_SUCCESS_MSG, res,
                      '执行失败:' + step1_txt)
        self.assertEqual('not exists', file_rm_result, "执行失败" + step2_txt)
        if self.is_reset_para:
            self.assertTrue(self.msg1, '执行失败:' + self.step3_txt)
            self.assertTrue(self.restart, '执行失败:' + self.step3_txt)
            self.assertTrue(self.status_result, '执行失败:' + self.step3_txt)
